
             
/**
 * 
 *
 * @File name:  TFixedAssetsDetailDao.java 
 * @Create on:  2011-10-31 09:21:937
 * @Author   :  许福辉
 *
 * @ChangeList
 * ---------------------------------------------------
 * Date         Editor              ChangeReasons
 *
 */
           
        package com.boho.estates.usual.dao;

            
import java.sql.Connection;
import java.util.LinkedList;
import java.util.List;
import cn.boho.framework.po.CQuery;
import cn.boho.framework.po.CQueryFactoryTool;
import cn.boho.framework.po.DAO;
import cn.boho.framework.po.DynaBeanMap;
import cn.boho.framework.po.CQExp;
import cn.boho.framework.po.Pager;
import cn.boho.framework.po.ComboPager;
import cn.boho.framework.utils.DateUtils;
            
        /**
 * 【资产详细信息:T_FIXED_ASSETS_DETAIL】的Dao操作类
 * 
 */
public class TFixedAssetsDetailDao extends DAO {
	
	
	/**
	* 查询所有不属于这个状态的资产【资产详细信息:T_FIXED_ASSETS_DETAIL】信息
	* @param con
	* @return
	* @throws Exception
	* @date 2011-10-31
	* @author 许福辉
	*
	*/
	public static List queryTFixedAssetsDetailByNotStatus(Connection con,String status)throws Exception{
		List list = new LinkedList();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_FIXED_ASSETS_DETAIL where is_valid=1 and FA_STATUS<>? order by BARCODE");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setString(1, status);
		query.setCommand(sql.toString());
		list = query.getDynaBeanMapList("T_FIXED_ASSETS_DETAIL", con);
		logger.debug("TFixedAssetsDetailDao.queryTFixedAssetsDetail() SQL->"+query.getCommand());
		return list;
	}
	/**
	* 分页查询所有【资产详细信息:T_FIXED_ASSETS_DETAIL】信息
	* @param con
	* @param pager
	* @return
	* @throws Exception
	* @date 2011-10-31
	* @author 许福辉
	*
	*/
	public static ComboPager queryTFixedAssetsDetailByPager(Connection con,Pager pager,String  BARCODE
			,String FIXED_ASSETS_NAME,String FIXED_ASSETS_TYPE
			,double STOCK_PRICE1,double STOCK_PRICE2
			,String REMARK,String FA_STATUS,String FIXED_ASSETS_NATURE,String SEQ_FIELD,String SEQ_WAY, String departIds)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct t1.*" +
				" from T_FIXED_ASSETS_DETAIL t1 where t1.is_valid=1 " +
				" ");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		int m=0;
		if(FIXED_ASSETS_TYPE != null && FIXED_ASSETS_TYPE.toString().length() > 0){
			sql.append(" and FIXED_ASSETS_TYPE= ?");
			query.setString(++m, FIXED_ASSETS_TYPE);
		}
		if(FIXED_ASSETS_NATURE != null && FIXED_ASSETS_NATURE.toString().length() > 0){
			sql.append(" and FIXED_ASSETS_NATURE = ?");
			query.setString(++m, FIXED_ASSETS_NATURE);
		}
		if(FA_STATUS != null && FA_STATUS.toString().length() > 0){
			sql.append(" and FA_STATUS= ?");
			query.setString(++m, FA_STATUS);
		}
		if(BARCODE != null && BARCODE.toString().length() > 0){
			sql.append(" and lower(BARCODE) like ?");
			query.setString(++m, "%"+BARCODE.toLowerCase()+"%");
		}
		if(FIXED_ASSETS_NAME != null && FIXED_ASSETS_NAME.toString().length() > 0){
			sql.append(" and FIXED_ASSETS_NAME like ?");
			query.setString(++m, "%"+FIXED_ASSETS_NAME+"%");
		}
		if(REMARK != null && REMARK.toString().length() > 0){
			sql.append(" and t1.REMARK = ?");
			query.setString(++m, "%"+REMARK+"%");
		}
		if(STOCK_PRICE1 != 0l && STOCK_PRICE2!=0l){
			sql.append(" and STOCK_PRICE >= ?");
			query.setDouble(++m, STOCK_PRICE1);
			sql.append(" and STOCK_PRICE <= ?");
			query.setDouble(++m, STOCK_PRICE2);
		}
		
		sql.append(" and GS_DEPARTMENT_NO in (").append("0," + departIds).append(")");
		
		if(!("").equals(SEQ_FIELD)){
			sql.append(" order by "+SEQ_FIELD+" "+SEQ_WAY);
		}else{
			sql.append(" order by BARCODE");
		}
		
		
		query.setCommand(sql.toString());
		logger.debug("TFixedAssetsDetailDao.queryTFixedAssetsDetailByPager() SQL->"+query.getCommand());
		return query.getDynaBeanMapComboPager("T_FIXED_ASSETS_DETAIL", pager,con);
	}
	/**
	* 分页查询所有【转移资产详细信息:T_FIXED_ASSETS_DETAIL】信息
	* @param con
	* @param pager
	* @return
	* @throws Exception
	* @date 2011-10-31
	* @author 许福辉
	*
	*/
	public static ComboPager queryTFaAllocateDetailByPager(Connection con,Pager pager,String  BARCODE
			,String FIXED_ASSETS_NAME)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct t1.*" +
				" from T_FA_ALLOCATE t1,T_FA_ALLOCATE_DETAIL t2 where t1.is_valid=1 " +
				" and t1.FA_ALLOCATE_NO=t2.FA_ALLOCATE_NO");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		int m=0;
		if(BARCODE != null && BARCODE.toString().length() > 0){
			sql.append(" and lower(t2.BARCODE) like ?");
			query.setString(++m, "%"+BARCODE.toLowerCase()+"%");
		}
		if(FIXED_ASSETS_NAME != null && FIXED_ASSETS_NAME.toString().length() > 0){
			sql.append(" and t2.FIXED_ASSETS_NAME like ?");
			query.setString(++m, "%"+FIXED_ASSETS_NAME+"%");
		}
		sql.append("  order by t1.CREATE_DATE desc");
		query.setCommand(sql.toString());
		logger.debug("TFixedAssetsDetailDao.queryTFixedAssetsDetailByPager() SQL->"+query.getCommand());
		return query.getDynaBeanMapComboPager("T_FIXED_ASSETS_DETAIL", pager,con);
	}
	/**
	* 分页查询所有【转移资产详细信息:T_FIXED_ASSETS_DETAIL】信息
	* @param con
	* @param pager
	* @return
	* @throws Exception
	* @date 2011-10-31
	* @author 许福辉
	*
	*/
	public static ComboPager queryTFaAllocateDetailByIdPager(Connection con,Pager pager,String  id)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct t2.*" +
				" from T_FA_ALLOCATE_DETAIL t2 where t2.is_valid=1 " +
				" and t2.FA_ALLOCATE_NO=?  order by t2.BARCODE");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setString(1, id);
		query.setCommand(sql.toString());
		logger.debug("TFixedAssetsDetailDao.queryTFixedAssetsDetailByPager() SQL->"+query.getCommand());
		return query.getDynaBeanMapComboPager("T_FIXED_ASSETS_DETAIL", pager,con);
	}
	/**
	* 分页查询所有【资产详细信息:T_FIXED_ASSETS_DETAIL】信息
	* @param con
	* @param pager
	* @return
	* @throws Exception
	* @date 2011-10-31
	* @author 许福辉
	*
	*/
	public static ComboPager queryTFixedAssetsDetailByTypePager(Connection con,Pager pager,String BARCODE,String type)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.*" +
				" from T_FIXED_ASSETS_DETAIL t1" +
				" where FIXED_ASSETS_TYPE=? and t1.is_valid=1 ");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setString(1, type);
		if(BARCODE != null && BARCODE.toString().length() > 0){
			sql.append(" and lower(BARCODE) like ?");
			query.setString(1, "%"+BARCODE.toLowerCase()+"%");
		}
		sql.append("  order by t1.BARCODE");
		query.setCommand(sql.toString());
		logger.debug("TFixedAssetsDetailDao.queryTFixedAssetsDetailByTypePager() SQL->"+query.getCommand());
		return query.getDynaBeanMapComboPager("T_FIXED_ASSETS_DETAIL", pager,con);
	}
	/**
	* 分页查询所有【转移变更详细信息:T_FIXED_CHANGE_FLOW】信息
	* @param con
	* @param pager
	* @return
	* @throws Exception
	* @date 2011-10-31
	* @author 许福辉
	*
	*/
	public static ComboPager queryTFixedChangeFlowByIdPager(Connection con,Pager pager,String barcode,String changetype)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.*" +
				" from T_FA_CHANGE_FLOW t1" +
				" where t1.is_valid=1 ");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		int m=0;
		if(barcode != null && barcode.toString().length() > 0){
			sql.append(" and lower(BARCODE) like ?");
			query.setString(++m, "%"+barcode.toLowerCase()+"%");
		}
		if(changetype != null && changetype.toString().length() > 0){
			sql.append(" and CHANGE_TYPE=?");
			query.setString(++m, changetype);
		}
		sql.append(" order by t1.BARCODE");
		query.setCommand(sql.toString());
		logger.debug("TFixedAssetsDetailDao.queryTFixedChangeFlowByIdPager() SQL->"+query.getCommand());
		return query.getDynaBeanMapComboPager("T_FA_CHANGE_FLOW", pager,con);
	}
	/**
	* 分页查询所有【资产详细信息:T_FIXED_ASSETS_DETAIL】信息
	* @param con
	* @param pager
	* @return
	* @throws Exception
	* @date 2011-10-31
	* @author 许福辉
	*
	*/
	public static ComboPager queryTFixedAssetsDetailByIdStrByPager(Connection con,Pager pager,String id)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.* from T_FIXED_ASSETS_DETAIL t1 where t1.IS_VALID=1");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		if(id != null && id.toString().length() > 0){
			sql.append(" and t1.ID in("+id+")");
		}
		sql.append("  order by t1.BARCODE");
		query.setCommand(sql.toString());
		logger.debug("TFixedAssetsDetailDao.queryTFixedAssetsDetailByIdStrByPager() SQL->"+query.getCommand());
		return query.getDynaBeanMapComboPager("T_FIXED_ASSETS_DETAIL", pager,con);
	}
	/**
	* 根据主键【条形码:BARCODE|】获得信息
	* @param con
	* @param barcode 条形码
	* @return
	* @throws Exception
	* @date 2011-10-31
	* @author 许福辉
	*
	*/
	public static DynaBeanMap getTFixedAssetsDetailByPk(Connection con,java.lang.Integer id,String BARCODE)throws Exception{
		DynaBeanMap dynaBeanMap = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.*" +
				" from T_FIXED_ASSETS_DETAIL t1 where t1.is_valid=1 ");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		int m=0;
		if(BARCODE != null && BARCODE.toString().length() > 0){
			sql.append(" and lower(BARCODE) like ?");
			query.setString(++m, "%"+BARCODE.toLowerCase()+"%");
		}
		if(id != -1){
			sql.append(" and ID=?");
			query.setInt(++m, id);
		}
		sql.append("  order by t1.BARCODE");
		query.setCommand(sql.toString());
		logger.debug("TFixedAssetsDetailDao.getTFixedAssetsDetailByPk() SQL->"+query.getCommand());
		dynaBeanMap = query.getDynaBeanMap("T_FIXED_ASSETS_DETAIL", con);
		return dynaBeanMap;
	}
	/**
	* 根据名称获得资产基本信息
	* @param con
	* @param name 资产名称
	* @return
	* @throws Exception
	* @date 2011-10-31
	* @author 许福辉
	*
	*/
	public static DynaBeanMap getTFixedAssetsByName(Connection con,java.lang.String name, int id)throws Exception{
		DynaBeanMap dynaBeanMap = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_FIXED_ASSETS_DETAIL where  FIXED_ASSETS_NAME=? and is_valid=1 and id <>?  order by BARCODE");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		query.setString(1,name);
		query.setInt(2, id);
		logger.debug("TFixedAssetsDetailDao.getTFixedAssetsByName() SQL->"+query.getCommand());
		dynaBeanMap = query.getDynaBeanMap("T_FIXED_ASSETS_DETAIL", con);
		return dynaBeanMap;
	}
}
